package com.hunsy.fileupload;

import com.hunsy.fileupload.DAO.ProductPropertyDAO;
import com.jfinal.core.Controller;
import com.jfinal.upload.UploadFile;
import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.*;

/**
 * Created by hunsy on 2017/4/28.
 */

public class IndexController extends Controller {

    private Logger logger = LoggerFactory.getLogger(BqConf.class);

    ProductPropertyDAO productPropertyDAO;
    ResourceBundle resource;

    /**
     * CONSTRUCTOR
     */
    public IndexController(){

        productPropertyDAO = new ProductPropertyDAO();
        resource = ResourceBundle.getBundle("field");

    }

    /**
     * 文件上传
     * upload文件夹为临时缓存点 在代码结尾有删除缓存
     */
    public void index() throws Exception{

        UploadFile uf = getFile("dataPoint_file");
        String product_id = getPara("product_id");
        String oname = uf.getOriginalFileName();
        String type = "";
        int idx = oname.lastIndexOf(".");

        if (idx > 0) {
            type = oname.substring(idx, oname.length());
        }

        if (type.equals(".xls")){
            type = ".xlsx";
        }

        //生成新的文件名
        String fileName = new Date().getTime() + UUID.randomUUID().toString().substring(0, 4) + type;

        try {
            //保存文件
            FileUtils.copyFile(uf.getFile(), new File(BqConf.FILE_DIR + fileName));

            //删除缓存
            uf.getFile().delete();

            //解析excel
            String result = ReadExcel(product_id,new File(BqConf.FILE_DIR + fileName));

            logger.info("上传文件:{}", fileName);
            renderJson(new JSONObject().put("file_path", fileName).put("import_result",result).toString());
        } catch (IOException e) {
            e.printStackTrace();
            renderError(500);
        }
    }

    /**
     * 获取文件
     */
    public void mfile() {
        String fileName = getPara("file_name");
        renderFile(new File(BqConf.FILE_DIR + fileName));
    }

    /**
     * EXCEL解析 + 导入数据库
     * 有空把两步骤分离开来
     * @param product_id 产品id
     * @param file EXCEL文件
     */
    public String ReadExcel(String product_id, File file){
        try {
            Workbook workbook = new XSSFWorkbook(file);             //工作簿..?
            Sheet sheet = workbook.getSheetAt(0);
            int rowLength = sheet.getLastRowNum();                  //行数
            int colLength;                                          //列数
            int success_count = 0;                                  //excel记录读取成功条数
            String[] col_name;                                      //列名
            List<JSONObject> list = new ArrayList<JSONObject>();    //excel已读取记录

            if (rowLength > 0){
                Row first_row = sheet.getRow(0);
                colLength = first_row.getLastCellNum();
                col_name = new String[colLength];

                for (int col_num=0;col_num<colLength;col_num++){
                    col_name[col_num] = first_row.getCell(col_num).getStringCellValue();
                }

                //逐行读出 第3行开始为需要保存的数据
                for (int row_num=2;row_num<=rowLength;row_num++){
                    try {
                        JSONObject jsonObject = new JSONObject();
                        Row row = sheet.getRow(row_num);

                        //逐列读取 会过滤空列
                        for (int col_num = 0;col_num<colLength;col_num++){
                            if (!col_name[col_num].equals("")){
                                jsonObject.put(col_name[col_num],row.getCell(col_num)==null?"":row.getCell(col_num).toString());
                            }
                        }

                        //将已读取记录存到列表中
                        list.add(jsonObject);
                    } catch (JSONException e) {
                        e.printStackTrace();
                    }
                }

                //将读取到的记录进行保存
                success_count = productPropertyDAO.insert(product_id,GetProperties(col_name).split("/"),list);
                logger.info("导入数据点:{}", "导入成功:"+success_count+",导入失败:"+(rowLength-2-success_count));
            }

            return new JSONObject()
                    .put("total_count",rowLength-1)
                    .put("success_count",success_count)
                    .put("failure_count",rowLength-1-success_count)
                    .toString();

        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (JSONException e){
            e.printStackTrace();
        }

        //其实根本到不了这，错误会直接被外层catch - -
        return "{\"msg\":\"error\"}";
    }

    //从配置文件中读取字段
    private String getField(){

        String field = null;

        try {
            field = new String(resource.getString("product_property").getBytes("ISO-8859-1"),"GBK");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        return field;
    }

    //依次拼接 sql 字段名部分(product_property)
    private String GetProperties(String[] col_name) {

        String properties = "";
        String cproperties = "";
        String field = getField();
        List<String> field_list = Arrays.asList(field.split(","));

        for (String one:field_list
                ) {
            if (one.equals(":")){
                continue;
            }
            for (String each: col_name
                 ) {
                if (one.split(":")[0].equals(each)){
                    properties += one.split(":")[1]+",";
                    cproperties += each+",";
                    break;
                }
            }
        }

        properties = properties.substring(0,properties.length()-1);
        cproperties = cproperties.substring(0,cproperties.length()-1);

        return properties + "/" + cproperties;
    }

}
